"""
公共方法 2024-07-15
"""

import os
import pandas as pd
from top.wardyan.core.wdb.core import db, Proc


CONFIG_FILE_PATH = "config\\数据清理工具.xlsx"
SQL_INSERT_ITEM = """INSERT INTO dbo.yt_clean_item (mID, dID, sIndex, xModular, xName, xTableName, xIsByDate, xDateField,  xDelRowCnt,xIsClean,xTableType, xIsBySID, xMainTableName)
VALUES (%d, %d, %d, %s, %s, %s, %d, %s, %d,%d,%d,%d,%s)"""
SQL_ALLTABLES = "SELECT t.name AS xTableName FROM sys.tables AS t INNER JOIN sys.columns AS c ON c.object_id = t.object_id WHERE c.name = 'sID'"
SQL_INSERTPRODTABLE = "INSERT INTO yt_clean_prodtable(mID,dID,sIndex,rPO_ID,xTableName,xMinDate,xMaxDate,xMinID,xMaxID,xRowCnt) VALUES(%d,%d,%d,%d,%s,%s,%s,%d,%d,%d)"

def get_clean_item_mains() -> list[dict] :
    """
    从配置文件中读取清理项目主表
    """
    df = pd.read_excel(CONFIG_FILE_PATH, engine='openpyxl', sheet_name="清理方案")
    return df.to_dict(orient="records")
def get_clean_item_details() -> list[dict]:
    """
    从配置文件中读取清理项目明细表
    """
    df = pd.read_excel(CONFIG_FILE_PATH, engine='openpyxl', sheet_name="清理方案-明细")
    return df.to_dict(orient="records")

def get_clean_item_others() -> list[dict]:
    """
    从配置文件中读取清理项目明细表
    """
    df = pd.read_excel(CONFIG_FILE_PATH, engine='openpyxl', sheet_name="清理方案-特殊")
    return df.to_dict(orient="records")

    
def get_clean_countpo() -> list[dict]:
    """
    从配置文件中读取清理项目明细表
    """
    df = pd.read_excel(CONFIG_FILE_PATH, engine='openpyxl', sheet_name="分批统计")
    return df.to_dict(orient="records")


def init_clean_items_formain(id : int, main : dict, details : list[dict]) :
    """
    处理一个主表记录
    注意这里不包括条码信息的. 
    """
    did = main["序号1"] * 10000 + main["序号2"]
    d, cursor = db.getcursor()
    cursor.execute(SQL_INSERT_ITEM, (id, did, did, main["模块"], main["名称"], main["数据表"], main["可以按日期清理"], main["日期字段"], main["每次删除行数"], main["默认清理"], 1, main["按sID清理"], ""))
    d.commit()

    ## 相关的明细表
    mname = main["数据表"]
    for detail in details :
        dname : str = detail["数据表"]
        if dname.startswith(mname + "_") :
            did = detail["序号1"] * 10000 + detail["序号2"]
            cursor.execute(SQL_INSERT_ITEM, (id, did, did, detail["模块"], detail["名称"], detail["数据表"], 0, "", main["每次删除行数"], main["默认清理"], 2, main["按sID清理"], detail["主表名"]))
            d.commit()
    
    db.backcursor(d, cursor)



def init_clean_items_forspec(id : int, spec : dict) :
    """
    处理特殊的. 譬如条码
    """
    did = spec["序号1"] * 10000 + spec["序号2"]
    d, cursor = db.getcursor()
    cursor.execute(SQL_INSERT_ITEM, (id, did, did, spec["模块"], spec["名称"], spec["数据表"], 0, "", spec["每次删除行数"], spec["默认清理"], 3, 0, ""))
    d.commit()
    db.backcursor(d, cursor)



# def init_clean_checksid(id : int) :
#     d, cursor = db.getcursor()
    
#     SIDTABLES = []
#     cursor.execute(SQL_ALLTABLES)
#     ts = cursor.fetchall()
#     for t in ts :
#         SIDTABLES.append(t[0])

#     cursor.execute("SELECT xTableName FROM dbo.yt_clean_item where mID = %d AND xTableType = 1", (id))
#     tablenames = cursor.fetchall()
#     for tablename in tablenames :
#         tn = tablename[0]
#         if tn in SIDTABLES :
#             cursor.execute("UPDATE yt_clean_item " % tn)

#     db.backcursor(d, cursor)
    

def init_clean_main(xDataType : int, xEBillDate : str) :
    """
    初始化统计
    """
    id = db.sp_getkeyvalue("up_query1", "sID")["rResult"]
    print("生成ID:", id)
    sql = "INSERT INTO dbo.yt_clean (sID, xDatetime, xDataType, xEBillDate) VALUES (%d,GETDATE(),%d,%s)"
    d, cursor = db.getcursor()
    cursor.execute(sql, (id, xDataType, xEBillDate))
    d.commit()
    db.backcursor(d,cursor)
    return id


def init_clean_items(id : int) :
    """
    从配置文件中初始化清理项目明细,包括主表和明细表
    @param id: 清理项目ID
    """

    # if id is None or id <= 0 :
    #     id = db.sp_getkeyvalue("up_query1", "sID")["rResult"]
    #     print("生成ID:", id)
    #     sql = "INSERT INTO dbo.yt_clean (sID, xDatetime, xDataType, xEBillDate) VALUES (%d,%s,1,)"

    mains = get_clean_item_mains()
    details = get_clean_item_details()
    others = get_clean_item_others()
    idx = 1

    ## 循环处理每一个主表记录
    for main in mains :
        init_clean_items_formain(id, main, details)
        idx += 1

    for other in others :
        init_clean_items_forspec(id, other)
        idx += 1
    ## 注意有些要排除(另外处理的:条码信息)
    ## 处理条码信息
    # for main in mains :
    #     if main["模块"] == "条码信息" :
    #         print("")



def init_clean_init_rows(id : int) :
    """
    初步统计每个数据表的行数
    """
    d, cursor = db.getcursor()
    cursor.execute("exec yp_clean_init @pID=%d, @pIsReCnt=%d", (id, 1))
    d.commit()
    db.backcursor(d,cursor)

def init_clean_count(id : int) :
    """
    统计每个批号在每个数据表的最大最小ID
    """
    d, cursor = db.getcursor()
    ## 我x 这种统计就没必要从数据表里读了啊 直接循环就行了啊
    cursor.execute("SELECT xTableName, xMaxSID FROM yt_clean_item WHERE mID = %d AND xTableType=1 AND xMaxSID > 0", (id,))
    rows = cursor.fetchall()
    tables : dict = {}
    for row in rows :
        tables[row["xTableName"]] = row["xMaxSID"]

    # for k, v in tables.items() :
    #     cursor.execute("SELECT COUNT(*) FROM %s WHERE sID > %d", (k, v))
    #     row = cursor.fetchone()
    #     cursor.execute("UPDATE yt_clean_item SET xCnt = %d WHERE mID = %d AND xTableName = %s", (row[0], id, k))
    #     d.commit()

    cursor.execute("delete from yt_clean_prodtable where mID = %d", (id,))
    d.commit()
    idx = 1
    posqls = get_clean_countpo()
    for posql in posqls :
        tbname = posql["表名"]
        sql = posql["统计语句"]
        if tbname in tables.keys() :
            maxsid = tables[tbname]
            if maxsid is not None and maxsid > 0 :
                ## print(tbname, maxsid, sql)
                cursor.execute(sql, (maxsid,))
                row = cursor.fetchall()
                for r in row :
                    ## mID,dID,sIndex,rPO_ID,xTableName,xMinDate,xMaxDate,xMinID,xMaxID,xRowCnt"
                    cursor.execute(SQL_INSERTPRODTABLE, (id,idx,idx,r["rPO_ID"],tbname,r["xMinDate"],r["xMaxDate"],r["xMinID"],r["xMaxID"],r["xCnt"]))
                    idx += 1
                d.commit()

    ## BUILD
    cursor.execute("exec yp_clean_countprodtable @pID=%d, @pIsReCnt=%d", (id, 1))
    d.commit()

    db.backcursor(d, cursor)



def init_buildsql(id : int) :
    """
    生成SQL
    """
    
    d, cursor = db.getcursor()
    cursor.execute("exec yp_clean_buildsql @pID=%d, @pIsReCnt=%d", (id, 1))
    d.commit()
    db.backcursor(d,cursor)





    ## NEXT BUILD SQL
if __name__ == "__main__":
    ## init_clean_items(999)
    init_clean_count(999)